using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VQPPROF
    {
        public void SetUpNewRow(ref DataRow dwReturn)
        {

            switch (app.company)
            {
                case "YGR":
                case "YCC":
                case "TST":
                default:
                    #region=========
                    dwReturn["VAAMANDANT"] = "00";
                    dwReturn["VAAKOLL"] = "     ";
                    dwReturn["VAAART"] = "                        ";
                    dwReturn["VAAKURZBEZ"] = "     ";
                    dwReturn["VAABEZ"] = "                              ";
                    dwReturn["VAASPERRDAT"] = "0";
                    dwReturn["VAABESTANDF"] = "J";
                    dwReturn["VAAMEBRT"] = "   ";
                    dwReturn["VAABRTFKT"] = "000000000000";
                    dwReturn["VAABRTVON"] = "0";
                    dwReturn["VAABRTBIS"] = "0";
                    dwReturn["VAAMELAE"] = "   ";
                    dwReturn["VAALAEFKT"] = "000000000000";
                    dwReturn["VAALAEVON"] = "0";
                    dwReturn["VAALAEBIS"] = "0";
                    dwReturn["VAAMEHRAP"] = "   ";
                    dwReturn["VAAHRAPVON"] = "0";
                    dwReturn["VAAHRAPBIS"] = "0";
                    dwReturn["VAAMEBRAP"] = "   ";
                    dwReturn["VAABRAPVON"] = "0";
                    dwReturn["VAABRAPBIS"] = "0";
                    dwReturn["VAAMEGEW"] = "   ";
                    dwReturn["VAAGEWGRP"] = " ";
                    dwReturn["VAAGEWFKT"] = "000000000000";
                    dwReturn["VAAGEWVON"] = "0";
                    dwReturn["VAAGEWBIS"] = "0";
                    dwReturn["VAAMENGEH"] = "   ";
                    dwReturn["VAAMEGGRP"] = " ";
                    dwReturn["VAAMEGFKT"] = "000000000000";
                    dwReturn["VAAMEUEB"] = "   ";
                    dwReturn["VAAUEBGRP"] = " ";
                    dwReturn["VAAUEBFKT"] = "000000000000";
                    dwReturn["VAAEAN"] = "0000000000000";
                    dwReturn["VAASTATWNR"] = "            ";
                    dwReturn["VAAZOLLTNR1"] = "                    ";
                    dwReturn["VAAZOLLTNR2"] = "                    ";
                    dwReturn["VAAARTPRGR"] = "   ";
                    dwReturn["VAAPVGRP"] = "   ";
                    dwReturn["VAAAUFART"] = "   ";
                    dwReturn["VAAPACKART"] = "   ";
                    dwReturn["VAAURLAND"] = "   ";
                    dwReturn["VAAWECHSEL"] = " ";
                    dwReturn["VAAURTEXT"] = "         ";
                    dwReturn["VAAREFART"] = "                        ";
                    dwReturn["VAAPART"] = "                    ";
                    dwReturn["VAADKUNDNR"] = "00000000";
                    dwReturn["VAADWERK"] = "   ";
                    dwReturn["VAADABT"] = "   ";
                    dwReturn["VAADART"] = "                    ";
                    dwReturn["VAADVAR"] = "  ";
                    dwReturn["VAAGESANT1"] = "000";
                    dwReturn["VAAGESBEZ1"] = "      ";
                    dwReturn["VAAPOLANT1"] = "000";
                    dwReturn["VAAPOLBEZ1"] = "      ";
                    dwReturn["VAARUECKANT1"] = "000";
                    dwReturn["VAARUECKBEZ1"] = "      ";
                    dwReturn["VAAGESANT2"] = "000";
                    dwReturn["VAAGESBEZ2"] = "      ";
                    dwReturn["VAAPOLANT2"] = "000";
                    dwReturn["VAAPOLBEZ2"] = "      ";
                    dwReturn["VAARUECKANT2"] = "000";
                    dwReturn["VAARUECKBEZ2"] = "      ";
                    dwReturn["VAAGESANT3"] = "000";
                    dwReturn["VAAGESBEZ3"] = "      ";
                    dwReturn["VAAPOLANT3"] = "000";
                    dwReturn["VAAPOLBEZ3"] = "      ";
                    dwReturn["VAARUECKANT3"] = "000";
                    dwReturn["VAARUECKBEZ3"] = "      ";
                    dwReturn["VAAGESANT4"] = "000";
                    dwReturn["VAAGESBEZ4"] = "      ";
                    dwReturn["VAAPOLANT4"] = "000";
                    dwReturn["VAAPOLBEZ4"] = "      ";
                    dwReturn["VAARUECKANT4"] = "000";
                    dwReturn["VAARUECKBEZ4"] = "      ";
                    dwReturn["VAAGESANT5"] = "000";
                    dwReturn["VAAGESBEZ5"] = "      ";
                    dwReturn["VAAPOLANT5"] = "000";
                    dwReturn["VAAPOLBEZ5"] = "      ";
                    dwReturn["VAARUECKANT5"] = "000";
                    dwReturn["VAARUECKBEZ5"] = "      ";
                    dwReturn["VAAGESANT6"] = "000";
                    dwReturn["VAAGESBEZ6"] = "      ";
                    dwReturn["VAAPOLANT6"] = "000";
                    dwReturn["VAAPOLBEZ6"] = "      ";
                    dwReturn["VAARUECKANT6"] = "000";
                    dwReturn["VAARUECKBEZ6"] = "      ";
                    dwReturn["VAAGESANT7"] = "000";
                    dwReturn["VAAGESBEZ7"] = "      ";
                    dwReturn["VAAPOLANT7"] = "000";
                    dwReturn["VAAPOLBEZ7"] = "      ";
                    dwReturn["VAARUECKANT7"] = "000";
                    dwReturn["VAARUECKBEZ7"] = "      ";
                    dwReturn["VAAGESANT8"] = "000";
                    dwReturn["VAAGESBEZ8"] = "      ";
                    dwReturn["VAAPOLANT8"] = "000";
                    dwReturn["VAAPOLBEZ8"] = "      ";
                    dwReturn["VAARUECKANT8"] = "000";
                    dwReturn["VAARUECKBEZ8"] = "      ";
                    dwReturn["VAAHWANT"] = "      ";
                    dwReturn["VAAPOLANTG"] = "0";
                    dwReturn["VAARUEKANTG"] = "0";
                    dwReturn["VAAAENDZL"] = "0";
                    dwReturn["VAAAENDBEN"] = "        ";
                    dwReturn["VAAAENDDAT"] = "0";
                    dwReturn["VAANEUBEN"] = "        ";
                    dwReturn["VAANEUDAT"] = "0";
                    dwReturn["VAAARTST"] = "G";
                    dwReturn["VAAZUREGEL"] = " ";
                    dwReturn["VAAZIELGR"] = "    ";
                    dwReturn["VAAPRGR"] = "K  ";
                    dwReturn["VAAARTGRP"] = "R  ";
                    dwReturn["VAALO"] = "  ";
                    dwReturn["VAABRTCA"] = "N";
                    dwReturn["VAALAECA"] = "N";
                    dwReturn["VAAHRAPCA"] = " ";
                    dwReturn["VAABRAPCA"] = " ";
                    dwReturn["VAAGEWCA"] = "N";
                    dwReturn["VAAPREH"] = "   ";
                    dwReturn["VAAEXCL"] = " ";
                    dwReturn["VAATAX"] = "   ";
                    dwReturn["VAASONDER"] = " ";
                    dwReturn["VAALASCHE"] = " ";
                    dwReturn["VAAFARBKZ"] = " ";
                    dwReturn["VAAWEBLEV"] = "0";
                    dwReturn["VAAARTFAMFL"] = " ";
                    dwReturn["VAAARTFAM"] = "                        ";
                    dwReturn["VAAARTWEB"] = "                        ";
                    dwReturn["VAAWEBSTD"] = " ";
                    dwReturn["VAAARTCOL"] = "          ";
                    dwReturn["VAAARTFIN"] = "          ";
                    dwReturn["VAADIMAM"] = "  ";
                    dwReturn["VAADIMZAM"] = "  ";
                    dwReturn["VAADIMLG"] = "  ";
                    dwReturn["VAAKONFDES"] = " ";
                    dwReturn["VAAKONFRAP"] = " ";
                    dwReturn["VAAKONFCOL"] = " ";
                    dwReturn["VAAKONFLG"] = " ";
                    dwReturn["VAAKONFBRT"] = " ";
                    dwReturn["VAAKONFMRK"] = "   ";
                    dwReturn["VAABUSIMODE"] = "   ";
                    dwReturn["VAAALTERNAT"] = " ";
                    dwReturn["VAAARTDESS"] = "          ";
                    dwReturn["VAAARTSIZE"] = "       ";
                    dwReturn["VAADIMF1"] = "          ";
                    dwReturn["VAADIMF2"] = "          ";
                    dwReturn["VAADIMF3"] = "          ";
                    dwReturn["VAAFILLER"] = "                                                                         ";
                    #endregion
                    break;
            }
        }
        public DataRow Convert2QPPROF(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryQPPROF(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
         dwReturn["QAPMANDANT"] = " ".PadRight(2 ); 
         dwReturn["QAPARTST"] = " ".PadRight(1 ); 
         dwReturn["QAPKUNDNR"] = " ".PadRight(8 ); 
         dwReturn["QAPWERK"] = " ".PadRight(3 ); 
         dwReturn["QAPABT"] = " ".PadRight(3 ); 
         dwReturn["QAPART"] = " ".PadRight(20 ); 
         dwReturn["QAPVAR"] = " ".PadRight(3 ); 
         dwReturn["QAPPOS"] = " ".PadRight(0 ); 
         dwReturn["QAPANFART"] = " ".PadRight(4 ); 
         dwReturn["QAPBEM"] = " ".PadRight(20 ); 
         dwReturn["QAPBIS"] = " ".PadRight(4 ); 
         dwReturn["QAPNUMKZ"] = " ".PadRight(1 ); 
         dwReturn["QAPME"] = " ".PadRight(3 ); 
         dwReturn["QAPMIN"] = " ".PadRight(0 ); 
         dwReturn["QAPMAX"] = " ".PadRight(0 ); 
         dwReturn["QAPZUSKZ"] = " ".PadRight(1 ); 
         dwReturn["QAPZUSATZ"] = " ".PadRight(10 ); 
         dwReturn["QAPFILLER"] = " ".PadRight(57 ); 
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapQPPROF(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveQPPROF(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryQPPROF(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapQPPROF(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertQPPROF(dwLocal, ITXDBVERSION);
            }
            UpdateQPPROF(dwLocal);
        }
        public void InsertQPPROF(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                    or1.CommandText = "INSERT INTO QPPROF(VAKBMANDANT, VAKBKOLL, VAKBART, VAKBKUNDNR, VAKBFARBE, VAKBKDARTNR, VAKBKDARTBEZ, VAKBKDFARBNR, VAKBANDRUCK, VAKBMEGEW, VAKBMENGEH, VAKBGEWVON, VAKBGEWBIS, VAKBGEWCA, VAKBGEWFKT, VAKBGEWGRP, VAKBMEGFKT, VAKBMEGGRP, VAKBAENDZL, VAKBAENDBEN, VAKBAENDDAT, VAKBNEUBEN, VAKBNEUDAT, VAKBFILLER)"
                        + " VALUES (:VAKBMANDANT, :VAKBKOLL, :VAKBART, :VAKBKUNDNR, :VAKBFARBE, '                         ', '                         ', '                             ', ' ', '   ', '   ', 0, 0, ' ', '000000000000', ' ', '000000000000', ' ', 0, '        ', '000000', '        ', '      ', '                                          ')";
                    or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            
            Connect2Proxy(or1);
        }
        public DataSet QueryQPPROF(DataRow dwData, int option = 1)
        {
            DataSet SReturn = new DataSet();
            DataSet SResult = new DataSet();
            OracleCommand or1;

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 2:
                    or1.CommandText = " SELECT DISTINCT GS.* FROM EKPOS GA LEFT JOIN QPPROF GS ON EKPMANDANT=QAPMANDANT"
                    + " AND utl_raw.cast_to_varchar2(EKPART)=QAPART"
                    + "  AND utl_raw.cast_to_varchar2(EKPVAR)=QAPVAR"
                    + "  WHERE EKPSA='PO' AND EKPARTGR=1 AND EKPMANDANT=:EKPMANDANT AND  EKPAUFKRS=:EKPAUFKRS AND EKPAUFLFD=:EKPAUFLFD ";
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = dwData["EKPMANDANT"].ToString();
                    or1.Parameters.Add("EKPAUFKRS", OracleType.NVarChar).Value = dwData["EKPAUFKRS"].ToString();
                    or1.Parameters.Add("EKPAUFLFD", OracleType.NVarChar).Value = dwData["EKPAUFLFD"].ToString();
                    break;
            }

            SResult = Connect2Proxy(or1);
            if (SResult.Tables.Count > 0)
            {
                SResult.Tables[0].TableName = "QPPROF";
                SReturn.Merge(SResult);
            }
            return SReturn;
        }
        public void DeleteQPPROF(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM QPPROF   WHERE  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                     or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        } 
      
        public void UpdateQPPROF(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command==== 
                    or1.CommandText = "UPDATE QPPROF SET "
                    +"QAPMANDANT= :QAPMANDANT,"
                    +"QAPARTST= :QAPARTST,"
                    +"QAPKUNDNR= :QAPKUNDNR,"
                    +"QAPWERK= :QAPWERK,"
                    +"QAPABT= :QAPABT,"
                    +"QAPART= :QAPART,"
                    +"QAPVAR= :QAPVAR,"
                    +"QAPPOS= :QAPPOS,"
                    +"QAPANFART= :QAPANFART,"
                    +"QAPBEM= :QAPBEM,"
                    +"QAPBIS= :QAPBIS,"
                    +"QAPNUMKZ= :QAPNUMKZ,"
                    +"QAPME= :QAPME,"
                    +"QAPMIN= :QAPMIN,"
                    +"QAPMAX= :QAPMAX,"
                    +"QAPZUSKZ= :QAPZUSKZ,"
                    +"QAPZUSATZ= :QAPZUSATZ,"
                    +"QAPFILLER= :QAPFILLER,"
                        + " WHERE  "
                        + "  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                    #endregion

                    #region====Parameters====
          or1.Parameters.Add("QAPMANDANT", OracleType.NVarChar).Value = dwData["QAPMANDANT"].ToString(); 
          or1.Parameters.Add("QAPARTST", OracleType.NVarChar).Value = dwData["QAPARTST"].ToString(); 
          or1.Parameters.Add("QAPKUNDNR", OracleType.NVarChar).Value = dwData["QAPKUNDNR"].ToString(); 
          or1.Parameters.Add("QAPWERK", OracleType.NVarChar).Value = dwData["QAPWERK"].ToString(); 
          or1.Parameters.Add("QAPABT", OracleType.NVarChar).Value = dwData["QAPABT"].ToString(); 
          or1.Parameters.Add("QAPART", OracleType.NVarChar).Value = dwData["QAPART"].ToString(); 
          or1.Parameters.Add("QAPVAR", OracleType.NVarChar).Value = dwData["QAPVAR"].ToString(); 
          or1.Parameters.Add("QAPPOS", OracleType.NVarChar).Value = dwData["QAPPOS"].ToString(); 
          or1.Parameters.Add("QAPANFART", OracleType.NVarChar).Value = dwData["QAPANFART"].ToString(); 
          or1.Parameters.Add("QAPBEM", OracleType.NVarChar).Value = dwData["QAPBEM"].ToString(); 
          or1.Parameters.Add("QAPBIS", OracleType.NVarChar).Value = dwData["QAPBIS"].ToString(); 
          or1.Parameters.Add("QAPNUMKZ", OracleType.NVarChar).Value = dwData["QAPNUMKZ"].ToString(); 
          or1.Parameters.Add("QAPME", OracleType.NVarChar).Value = dwData["QAPME"].ToString(); 
          or1.Parameters.Add("QAPMIN", OracleType.NVarChar).Value = dwData["QAPMIN"].ToString(); 
          or1.Parameters.Add("QAPMAX", OracleType.NVarChar).Value = dwData["QAPMAX"].ToString(); 
          or1.Parameters.Add("QAPZUSKZ", OracleType.NVarChar).Value = dwData["QAPZUSKZ"].ToString(); 
          or1.Parameters.Add("QAPZUSATZ", OracleType.NVarChar).Value = dwData["QAPZUSATZ"].ToString(); 
          or1.Parameters.Add("QAPFILLER", OracleType.NVarChar).Value = dwData["QAPFILLER"].ToString(); 
                    #endregion
                    break; 
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
    }
}
